package Dao.Implement;

import Dao.adminDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import pojo.admin;
import pojo.playground;
import pojo.room;
import pojo.stadium;

import java.math.BigInteger;
import java.sql.Date;
import java.util.List;

@Repository
public class adminDaoImplement implements adminDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public int register(String id, String name, String pwd) {
        String sql="insert into admin values(?,?,?)";
        return jdbcTemplate.update(sql,id,name,pwd);
    }


    @Override
    public admin queryById(String id) {
        String sql="select * from admin where vx_id=?";
        try {
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<admin>(admin.class),id);
        } catch (EmptyResultDataAccessException e) {
            return  null;
        }
    }

    @Override
    public boolean testIdPwd(String id, String pwd) {
        String sql="select * from admin where vx_id=?";
        String password= null;
        try {
            password = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<admin>(admin.class),id).getPassword();
        } catch (EmptyResultDataAccessException e) {
            password="";
        }
        return password.equals(pwd);
    }


    @Override
    public int updatePlaygroundState(playground playground) {
        String sql="UPDATE playground set " +
                "price=?,"+
                "play_name=?,"+
                "stadium_id=?,"+
                "playground_display=? ," +
                " sport_id=?," +
                "bitmaps_today=?," +
                "bitmaps_tomorrow=? where playground_id=?";
        return jdbcTemplate.update(sql,playground.getPrice(),playground.getPlay_name(),playground.getStadium_id(),playground.getPlayground_display(),playground.getSport_id(),playground.getBitmaps_today(),playground.getBitmaps_tomorrow(),playground.getPlayground_id());
    }

    @Override
    public List<stadium> queryStadiumByName(String name) {
        String sql="select * from stadium where name like '%"+name+"%'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<stadium>(stadium.class));
    }

    @Override
    public List<stadium> queryStadiumWithNoArguments() {
        String sql="select * from stadium";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<stadium>(stadium.class));
    }

    @Override
    public List<playground> queryPlaygroundListByStadium(Long stadium_id) {
        String sql="select * from playground where stadium_id=?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<playground>(playground.class),stadium_id);
    }

    @Override
    public List<stadium> queryStadium(String vxId) {
        String sql="select * from stadium where manager=?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<stadium>(stadium.class),vxId);
    }

    @Override
    public int addStadium(stadium stadium) {
        String sql="insert into stadium(name,location,category,opentime,closetime,stadiumDisplay,sports_id,manager) values(?,?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql,stadium.getName(),stadium.getLocation(),stadium.getCategory(),stadium.getOpentime(),stadium.getClosetime(),stadium.getStadiumDisplay(),stadium.getSports_id(),stadium.getManager());
    }

    @Override
    public int addPlayground(playground playground) {
        String sql="insert into playground(price,play_name,playground_display,stadium_id,sport_id,bitmaps_today,bitmaps_tomorrow) values(?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql,playground.getPrice(),playground.getPlay_name(),playground.getPlayground_display(),playground.getStadium_id(),playground.getSport_id(),playground.getBitmaps_today(),playground.getBitmaps_tomorrow());
    }

    @Override
    public List<room> queryRoom(String id) {
       String sql="SELECT room_id,host_id,capacity,current_number,date,start_time,end_time,money,stadium,playground,room.sports_id,roomstate,pk_match FROM room left join stadium on room.stadium=stadium.`name` where manager=?";
       return jdbcTemplate.query(sql,new BeanPropertyRowMapper<room>(room.class),id);
    }

    @Override
    public List<room> queryRoomByDate(String date) {
        String sql="select * from room where date=?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<room>(room.class),date);
    }

    @Override
    public List<room> queryRoomByRoomState(int roomstate,String Id) {
        String sql="SELECT room_id,host_id,capacity,current_number,date,start_time,end_time,money,stadium," +
        "playground,room.sports_id,roomstate,pk_match FROM room left join stadium on room.stadium=stadium.`name` " +
                "where manager=? and room.roomstate=?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<room>(room.class),Id,roomstate);
    }

    @Override
    public List<room> queryRoomByStadiumName(String stadiumName) {
        String sql="select * from room where stadium =?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<room>(room.class),stadiumName);
    }

    @Override
    public room queryRoomById(Long roomId) {
        String sql="select * from room where room_id=?";
        try {
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<room>(room.class),roomId);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public int deleteStadium(Long stadiumId) {
        String sql="delete from stadium where stadium_id=?";
        return jdbcTemplate.update(sql,stadiumId);
    }

    @Override
    public int deletePlayground(Long playgroundId,Long stadiumId) {
        String sql="delete from playground where playground_id=? and stadium_id=?";
        return jdbcTemplate.update(sql,playgroundId,stadiumId);
    }

    @Override
    public int updateStadium(stadium stadium) {
        String sql="update stadium set " +
                "manager=?," +
                "name=? ," +
                "location=? ," +
                "category=? ," +
                "opentime=? ," +
                "closetime=? ," +
                "stadiumDisplay=? ," +
                "sports_id=? where stadium_id=?";
        return jdbcTemplate.update(sql,stadium.getManager(),stadium.getName(),stadium.getLocation(),stadium.getCategory(),stadium.getOpentime(),stadium.getClosetime(),stadium.getStadiumDisplay(),stadium.getSports_id(),stadium.getStadium_id());

    }

    @Override
    public stadium queryStadiumById(Long stadiumId) {
        String sql="select * from stadium where stadium_id=?";
        try {
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<stadium>(stadium.class),stadiumId);
        } catch (EmptyResultDataAccessException e) {
           return null;
        }
    }

    @Override
    public playground queryPlaygroundById(Long playgroundId) {
        String sql="select * from playground where playground_id=?";
        try {
            return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<playground>(playground.class),playgroundId);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<room> queryRoomOrderTap(Date startTime, Date endDate,String Id) {
        String sql="SELECT room_id,host_id,capacity,current_number,date,start_time,end_time,money,stadium," +
                "playground,room.sports_id,roomstate,pk_match FROM room left join stadium on room.stadium=stadium.`name` " +
                "where manager=? and room.date>=? and room.date<=?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<room>(room.class),Id,startTime,endDate);
    }
}
